package com.phoenix.callAnalysis.db;

import java.util.ArrayList;

import com.phoenix.callAnalysis.models.Constants;
import com.phoenix.callAnalysis.models.MyCall;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.util.Log;

public class MyDB {
	private static final String TAG = MyDB.class.getSimpleName();
	private static final String CREATE_CALLS_TABLE = "create table "
			+ Constants.CALLS_TABLE_NAME + " (" + Constants.CALLS_KEY_ID
			+ " integer primary key autoincrement, " + Constants.CALLS_NAME
			+ " text, " + Constants.CALLS_NUMBER_LABEL + " text, "
			+ Constants.CALLS_NUMBER_TYPE + " int, "
			+ Constants.CALLS_OCCURED_DATE + " long, "
			+ Constants.CALLS_DURATION + " long, " + Constants.CALLS_NEW
			+ " int, " + Constants.CALLS_NUMBER + " text, "
			+ Constants.CALLS_TYPE + " int, " + Constants.CALLS_UPDATE_TIME
			+ " long not null, " + Constants.CALLS_CREATE_TIME
			+ " long not null);";

	private SQLiteDatabase db;
	private final Context context;
	private final MyDBhelper dbhelper;

	public MyDB(Context c) {
		Log.d(TAG, TAG + "()");
		context = c;
		dbhelper = new MyDBhelper(context, Constants.DATABASE_NAME, null,
				Constants.DATABASE_VERSION);
	}

	public void createTable() {
		Log.i(TAG, "starting to creat all the tables");
		try {
			Log.i(TAG, "start to create calls table : " + CREATE_CALLS_TABLE);
			db.execSQL("drop table if exists " + Constants.CALLS_TABLE_NAME);
			db.execSQL(CREATE_CALLS_TABLE);
			Log.i(TAG, "finish creating calls table");
		} catch (SQLiteException ex) {
			Log.i("Create table exception", ex.getMessage());
			ex.printStackTrace();
		}
		Log.d(TAG, "finish creating all the tables");
	}
	
	public void deleteCallsTable() {
		try {
			Log.i(TAG, "start to delete from calls......");
			db.execSQL("DELETE FROM "+Constants.CALLS_TABLE_NAME);
			Log.i(TAG, "finish deleting from calls......");
		} catch (SQLiteException ex) {
			Log.i("delete table calls exception", ex.getMessage());
			ex.printStackTrace();
		}
	}
	
	public void open() throws SQLiteException {
		try {
			db = dbhelper.getWritableDatabase();
		} catch (SQLiteException ex) {
			Log.e(TAG + "Open database exception caught", ex.getMessage());
			db = dbhelper.getReadableDatabase();
		}
	}

	public void close() {
		if (db.isOpen()) {
			db.close();
		} else {
			db = null;
		}
	}

	/**
	 * 
	 * @return calls count(ERROR:-1)
	 */
	public int getCallsCount() {
		int count = -1;
		Cursor c = null;
		try {
			c = db.query(Constants.CALLS_TABLE_NAME, null, null, null, null,
					null, null);
			count = c.getCount();
			return count;
		} catch (SQLiteException e) {
			Log.e(TAG, "getCallsCount()...");
			e.printStackTrace();
			return count;
		} finally {
			if (c != null && !c.isClosed()) {
				c.close();
			}
		}
	}

	public ArrayList<String> getAllNumbers() {
		ArrayList<String> numbers = new ArrayList<String>();
		Cursor cursor = null;
		String sql = "select DISTINCT " + Constants.CALLS_NUMBER
				+ " from " + Constants.CALLS_TABLE_NAME + ";";
		try {
			cursor = db.rawQuery(sql, null);
			if(cursor.getCount()==0) {
				return numbers;
			}
			cursor.moveToFirst();
			while(!cursor.isAfterLast()) {
					numbers.add(cursor.getString(0));
			}
			return numbers;
		} catch (SQLiteException e) {
			Log.e(TAG, "getAllNumbers()...");
			e.printStackTrace();
			return numbers;
		} finally {
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
		}	
	}
	
	public int getNumberCount() {
		return getAllNumbers().size();
	}

	/**
	 * 
	 * @param call
	 * @return true(success),false(error)
	 */
	public boolean insertOneCall(MyCall call) {
		try {
			ContentValues values = new ContentValues();
			values.put(Constants.CALLS_NAME, call.getName());
			values.put(Constants.CALLS_NUMBER_LABEL, call.getNumberLabel());
			values.put(Constants.CALLS_NUMBER_TYPE, call.getNumberType());
			values.put(Constants.CALLS_OCCURED_DATE, call.getOccuredDate());
			values.put(Constants.CALLS_DURATION, call.getDuration());
			values.put(Constants.CALLS_NEW, call.getIsNew());
			values.put(Constants.CALLS_NUMBER, call.getNumber());
			values.put(Constants.CALLS_TYPE, call.getType());
			values.put(Constants.CALLS_UPDATE_TIME, System.currentTimeMillis());
			values.put(Constants.CALLS_CREATE_TIME, System.currentTimeMillis());
			db.insertOrThrow(Constants.CALLS_TABLE_NAME, null, values);
			return true;
		} catch (SQLiteException e) {
			Log.e(TAG, "method insertOneCall(MyCall call) ERROR!");
			e.printStackTrace();
			return false;
		}
	}

	public MyCall getLastestCall() {
		MyCall call = new MyCall();
		Cursor calls = null;
		try {
			calls = db.query(Constants.CALLS_TABLE_NAME, null, null, null,
					null, null, Constants.CALLS_OCCURED_DATE + " DESC");
			if (calls.getCount() == 0)
				return call;
			calls.moveToFirst();
			call.setName(calls.getString(calls
					.getColumnIndex(Constants.CALLS_NAME)));
			call.setNumberLabel(calls.getString(calls
					.getColumnIndex(Constants.CALLS_NUMBER_LABEL)));
			call.setNumberType(calls.getInt(calls
					.getColumnIndex(Constants.CALLS_NUMBER_TYPE)));
			call.setOccuredDate(calls.getLong(calls
					.getColumnIndex(Constants.CALLS_OCCURED_DATE)));
			call.setDuration(calls.getLong(calls
					.getColumnIndex(Constants.CALLS_DURATION)));
			call.setIsNew(calls.getInt(calls
					.getColumnIndex(Constants.CALLS_NEW)));
			call.setNumber(calls.getString(calls
					.getColumnIndex(Constants.CALLS_NUMBER)));
			call.setType(calls.getInt(calls
					.getColumnIndex(Constants.CALLS_TYPE)));
			return call;
		} catch (SQLiteException e) {
			Log.e(TAG, "getLastestCall()...");
			e.printStackTrace();
			return call;
		} finally {
			if (calls != null && !calls.isClosed()) {
				calls.close();
			}
		}
	}

}
